package dk.miljoeportalen.dkpunkt.migration;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.jpa.JpaDialect;
import org.springframework.transaction.annotation.Transactional;

public class MigrationManager {

	private EntityManager entityManager;
	private JpaDialect jpaDialect;
	
	@PersistenceContext
	public void setEntityManager(EntityManager entityManager) {
		this.entityManager = entityManager;
	}
	
	@Autowired
	public void setJpaDialect(JpaDialect jpaDialect) {
		this.jpaDialect = jpaDialect;
	}
	
    public MigrationManager() {}
    
	public static final String migrationPackage = "dk.miljoeportalen.dkpunkt.migration.scripts";
    
    private static final Logger logger = Logger.getLogger(MigrationManager.class);
    
    private void ensureDbVersionTableExists() throws SQLException {
    	String query = "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'dbo' AND table_name = 'db_version'";    	
        Connection con = getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        if (!rs.next()) {
        	throw new RuntimeException("Failed to check if db_version table exists");
        }
        boolean exists = rs.getInt(1) == 1;
        rs.close();
        if(!exists) {
        	stmt.executeUpdate("CREATE TABLE db_version (version int not null)");
        	stmt.executeUpdate("INSERT INTO db_version VALUES (0)");
        }
        stmt.close();
    }
    
    public InputStream getMigrateScript(int version) {
        String packagePath = migrationPackage.replaceAll("\\.", "/");
        String resourcePath = packagePath + "/migrateto" + version + ".sql";
        logger.debug("getting migrate script " + resourcePath);
        InputStream stream = Thread.currentThread().getContextClassLoader().getResourceAsStream(resourcePath);
        if(stream == null) {
            logger.debug("failed to get migrate script: " + resourcePath);
        }
        return stream;
    }
    
    private void migrateUp(int targetVersion, InputStream sql) throws IOException, SQLException {
    	logger.info("Upgrading to version " + targetVersion);
        if(sql == null) {
            throw new RuntimeException("sql param to migrateUp was null");
        }
        Connection con = getConnection();
        logger.info("Acquired database connection");
		Reader reader = new InputStreamReader(sql, Charset.forName("UTF-8"));
		StringBuilder builder = new StringBuilder();
		char[] buffer = new char[1024];
		int read;
		while ((read = reader.read(buffer)) != -1) {
			builder.append(buffer, 0, read);
		}
		reader.close();
		Statement stmt = con.createStatement();
		logger.info("executing SQL: " + builder.toString());
		stmt.execute(builder.toString());
		stmt.executeUpdate("update db_version set version = " + targetVersion);
		stmt.close();
		logger.info("Upgraded schema to version " + targetVersion);
    }
    
    @Transactional(rollbackFor = Throwable.class)
    public void migrate() throws SQLException, IOException {
    	ensureDbVersionTableExists();
        boolean upgraded = false;
        while(true) {
            int currentVersion = getCurrentVersion();
            int targetVersion = currentVersion + 1;
            InputStream sql = getMigrateScript(targetVersion);
            if(sql == null) {
                break;
            }
            upgraded = true;
            migrateUp(targetVersion, sql);
        }
        if(!upgraded) {
            logger.info("current version of database:" + getCurrentVersion() + ", no database upgrade necessary.");
        }
    }

    private Connection getConnection() throws SQLException {
    	return jpaDialect.getJdbcConnection(entityManager, false).getConnection();
    }
    
    private int getCurrentVersion() throws SQLException {
        Connection con = getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT version FROM db_version");
        if (!rs.next()) {
        	throw new RuntimeException("Failed to retrive database version number");
        }
        int result = rs.getInt(1);
        rs.close();
        stmt.close();
        return result;
    }
}
